package DatabaseManager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import Other.*;
import DataConnectionInterface.UserInfo;
import Helper.Misc;

public class UserTable extends Table {

	/**
	 * The function builds a table object (doesn't actually create it in the
	 * database)
	 * 
	 * @param db_mgr
	 *            - the database manager
	 * @throws SQLException
	 *             - thrown in case of an sql exception
	 * @throws ClassNotFoundException
	 *             - thrown in case of a class not found exception
	 */
	public UserTable(DBManager db_mgr) throws SQLException,
			ClassNotFoundException {
		super(db_mgr);

		table_name = "users";
		primaryKey = "user_id";
		String[] tmp = { "username", "password", "wins", "losses",
				"last_login", "user_description", "user_id" };
		columns = tmp;

		foreignKey = new String[columns.length];
		for (int i = 0; i < foreignKey.length; i++)
			foreignKey[i] = null;

		String[] tmp1 = { "VARCHAR(20)", "VARCHAR(20)", "INT", "INT", "DATE", "TEXT",
				"INT" };
		dataTypes = tmp1;

		isDataTypeNull = new boolean[columns.length];
		for (int i = 1; i < isDataTypeNull.length; i++)
			isDataTypeNull[i] = false;
	}

	/**
	 * Returns list of most winning maxNumberOfHighScores users
	 * 
	 * @param maxNumberOfHighScores
	 * @return
	 * @throws SQLException
	 */
	public List<UserInfo> getHighScores(int maxNumberOfHighScores)
			throws SQLException {
		String query;
		query = "SELECT username, wins, user_description " + "FROM "
				+ table_name + " ORDER BY wins DESC LIMIT "
				+ maxNumberOfHighScores;
		ResultSet rs = db_mgr.QueryTable(query);
		List<UserInfo> userInfos = new LinkedList<UserInfo>();
		while (rs.next()) {
			UserInfo userInfo = new UserInfo(rs.getString(1), null, rs
					.getString(3));
			userInfo.setWins(rs.getInt(2));
			userInfos.add(userInfo);

		}
		db_mgr.closeLastStatement();
		return userInfos;
	}

	/**
	 * Increments a user's win or loss count
	 * 
	 * @param user_id
	 *            - id of user
	 * @param hasWon
	 *            - true <=> user has won
	 * @throws SQLException
	 */
	public void updateUserStatistics(int user_id, boolean hasWon)
			throws SQLException {
		String column = hasWon ? "wins" : "losses";
		db_mgr.UpdateTable(table_name, new String[] { column },
				new String[] { column + "+1" }, "user_id = " + user_id);
	}

	/**
	 * returns true <=> the user exists with this password or the user is new.
	 * Also updates the ID in ui in case the authentication succeeds.
	 * 
	 * @param ui
	 * @return
	 * @throws SQLException
	 */
	public boolean authenticate(UserInfo ui) throws SQLException {
		String username = Misc.correctString(ui.getName());
		String password = ui.getPassword();
		String message = ui.getDescription();
		String query;
		query = "SELECT user_id, password " + "FROM " + table_name + " WHERE "
				+ table_name + ".username = '" + username + "'";
		ResultSet rs = null;
		try {
			rs = db_mgr.QueryTable(query);
			boolean isEmpty = true;
			boolean ret = false;
			while (rs.next()) {
				isEmpty = false;
				ret = rs.getString(2).equals(password);
				ui.setID(rs.getInt(1));
			}
			db_mgr.closeLastStatement();
			if (!isEmpty) {
				if (ret) {
					// true if we want to update the description
					boolean isUpdateMessage = (message != null && !message.equals("")); 
					String[] attributes, values;
					if (isUpdateMessage) {
						attributes = new String[] {"last_login", "user_description"};
						values = new String[] {"'" + Misc.getTodaysDate() + "'", "'" + Misc.correctString(message) + "'"}; 
					}
					else {
						attributes = new String[] {"last_login"};
						values = new String[] {"'" + Misc.getTodaysDate() + "'"};
					}
					// update
					String where = "username = '" + username + "'";
					db_mgr.UpdateTable(table_name, attributes, values, where);
				}
				return ret;
			}

			String[] values = { username, Misc.correctString(password), "0",
					"0", Misc.getTodaysDate(), Misc.correctString(message) };
			ui.setID(db_mgr.InsertTableAndReturnID(table_name, columns, values,
					"user_id"));
		} catch (SQLException e) {
			db_mgr.closeLastStatement();
			throw e;
		}

		return true;
	}

	/**
	 * Returns information about all users in a page
	 * 
	 * @param pageNumber
	 *            - number of page
	 * @param entriesPerPage
	 *            - number of entries per page
	 * @return
	 * @throws SQLException
	 */
	public List<User> getUserPage(int pageNumber, int entriesPerPage)
			throws SQLException {
		ResultSet rs = super.getPage(pageNumber, entriesPerPage, "username",
				"*", null, null);
		List<User> users = new ArrayList<User>();
		// again, ensure rs's closing
		try {
			while (rs.next()) {
				String username = rs.getString(1);
				String password = rs.getString(2);
				String description = rs.getString(6);
				String login = rs.getString(5);
				int wins = rs.getInt(3);
				int losses = rs.getInt(4);
				int ID = rs.getInt(7);
				User user = new UserConcrete(username, password, description,
						login, wins, losses, ID);
				users.add(user);
			}
			// mechanism to ensure closing
		} catch (SQLException e) {
			db_mgr.closeLastStatement();
			throw e;
		}
		db_mgr.closeLastStatement();
		return users;
	}

}
